"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : 空串和null
Case Name   : 兼容b库下设置参数并验证null和空字符串
Create At   : 2023.08.07
Owner       : z@wan005
Description :
    1、创建兼容b数据库
    2、连接b库查询参数默认值
    3、b库下建表并插入空字符串
    4、查询
    5、b库重新建表并插入空串
    6、设置参数并查询
    7、设置参数为空并查询
    8、查询
    9、清理环境
Expect      :
    1、成功
    2、成功
    3、成功
    4、成功，c列类型为固定字符，故空串要补足10个，长度为10
    5、成功
    6、成功，(非a库可以设置，但不生效)，和默认行为保持一致，空串和null不同
    7、成功，(非a库可以设置，但不生效)，和默认行为保持一致，空串和null不同
    8、成功，不设置pad_char_to_full_length参数值，查询删除char类型尾部空格，
    空串和null值不同
    9、成功
History     :
"""

import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class CompatibilityTest50(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.sh_primary = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.db_name = "db_guc_0050"
        self.tb_name = "tb_guc0050"

    def test_atan(self):
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        text = '-----step1:创建兼容b数据库 expect:成功-----'
        self.log.info(text)
        create_cmd = f"drop database if exists {self.db_name};" \
                     f"create database {self.db_name} dbcompatibility 'b';"
        msg = self.sh_primary.execut_db_sql(create_cmd)
        self.log.info(msg)
        self.assertIn('CREATE DATABASE', msg, '执行失败:' + text)

        text = '-----step2:连接b库查询参数默认值 expect:成功-----'
        self.log.info(text)
        sql_cmd = "show behavior_compat_options;" \
                  "show dolphin.sql_mode;"
        msg = self.sh_primary.execut_db_sql(sql_cmd,
                                            dbname=self.db_name)
        self.log.info(msg)
        self.assertIn('display_leading_zero,select_into_return_null', msg,
                      '执行失败:' + text)
        self.assertIn('pad_char_to_full_length', msg, '执行失败:' + text)

        text = '-----step3:b库下建表并插入空字符串 expect:成功-----'
        self.log.info(text)
        sql_cmd = f"drop table if exists {self.tb_name};" \
                  f"create table {self.tb_name}(c char(10));" \
                  f"insert into {self.tb_name} values('');"
        msg = self.sh_primary.execut_db_sql(sql_cmd,
                                            dbname=self.db_name)
        self.log.info(msg)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS,
                      msg, '执行失败:' + text)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG,
                      msg, '执行失败:' + text)

        text = '--step4:查询 expect:成功，c列类型为固定字符，故空串要补足10个，' \
               '长度为10，b库下受pad_char_to_full_length参数值影响，' \
               '默认查询不删除char类型尾部空格，空串和null值不同-'
        self.log.info(text)
        sql_cmd = f"select length(c) from {self.tb_name} where c = '';" \
                  f"select length(c) from {self.tb_name} where c = ' ';" \
                  f"select * from {self.tb_name} where c is null;"
        msg = self.sh_primary.execut_db_sql(sql_cmd,
                                            dbname=self.db_name)
        self.log.info(msg)
        self.assertEqual('10', msg.splitlines()[2].strip(), '执行失败:' + text)
        self.assertEqual('10', msg.splitlines()[7].strip(), '执行失败:' + text)
        self.assertEqual('(0 rows)', msg.splitlines()[-1].strip(),
                         '执行失败:' + text)

        text = '-step5:b库重新建表并插入空串;expect:成功-'
        self.log.info(text)
        sql_cmd = f"drop table if exists {self.tb_name}_01;" \
                  f"create table {self.tb_name}_01(c char(10));" \
                  f"insert into {self.tb_name}_01 values('');"
        msg = self.sh_primary.execut_db_sql(sql_cmd,
                                            dbname=self.db_name)
        self.log.info(msg)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS,
                      msg, '执行失败:' + text)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG,
                      msg, '执行失败:' + text)

        text = '--step6:设置新增参数值并查询 expect:成功，(非a库可以设置，但不生效)，' \
               '和默认行为保持一致，空串和null不同-'
        self.log.info(text)
        sql_cmd = f"set behavior_compat_options='accept_empty_str';" \
                  f"select length(c) from {self.tb_name} where c = '';" \
                  f"select length(c) from {self.tb_name} where c = ' ';" \
                  f"select * from {self.tb_name} where c is null;"
        msg = self.sh_primary.execut_db_sql(sql_cmd,
                                            dbname=self.db_name)
        self.log.info(msg)
        self.assertIn('SET', msg, '执行失败:' + text)
        self.assertEqual('10', msg.splitlines()[3].strip(), '执行失败:' + text)
        self.assertEqual('10', msg.splitlines()[8].strip(), '执行失败:' + text)
        self.assertEqual('(0 rows)', msg.splitlines()[-1].strip(),
                         '执行失败:' + text)

        text = '--step7:设置参数为空并查询 expect:成功，(非a库可以设置，但不生效)，' \
               '和默认行为保持一致，空串和null不同-'
        self.log.info(text)
        sql_cmd = f"set behavior_compat_options='';" \
                  f"select length(c) from {self.tb_name} where c = '';" \
                  f"select length(c) from {self.tb_name} where c = ' ';" \
                  f"select * from {self.tb_name} where c is null;"
        msg = self.sh_primary.execut_db_sql(sql_cmd,
                                            dbname=self.db_name)
        self.log.info(msg)
        self.assertEqual('10', msg.splitlines()[3].strip(), '执行失败:' + text)
        self.assertEqual('10', msg.splitlines()[8].strip(), '执行失败:' + text)
        self.assertEqual('(0 rows)', msg.splitlines()[-1].strip(),
                         '执行失败:' + text)

        text = '--step8:查询 expect:成功，不设置pad_char_to_full_length参数值，' \
               '查询删除char类型尾部空格，空串和null值不同-'
        self.log.info(text)
        sql_cmd = f"set dolphin.sql_mode='';" \
                  f"select length(c) from {self.tb_name} where c = '';" \
                  f"select length(c) from {self.tb_name} where c = ' ';" \
                  f"select * from {self.tb_name} where c is null;"
        msg = self.sh_primary.execut_db_sql(sql_cmd,
                                            dbname=self.db_name)
        self.log.info(msg)
        self.assertEqual('0', msg.splitlines()[3].strip(), '执行失败:' + text)
        self.assertEqual('0', msg.splitlines()[8].strip(), '执行失败:' + text)
        self.assertEqual('(0 rows)', msg.splitlines()[-1].strip(),
                         '执行失败:' + text)

    def tearDown(self):
        text = '----step9:清理环境;expect:成功----'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};"
        msg = self.sh_primary.execut_db_sql(sql_cmd, dbname='postgres')
        self.log.info(msg)
        self.assertIn('DROP DATABASE', msg, '执行失败:' + text)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
